package com.hnkywl.jip.dao;

import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.springframework.stereotype.Repository;

import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-2-25
 * Time: 下午8:46
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class InvoiceMergeDao extends SimpleHibernateTemplate {

    /**
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param companyId 公司ID
     * @param operator  操作人
     * @return
     */
    public Page<Object[]> findInvoiceMerge(Date startTime, Date endTime, Integer status,
                                           Integer companyId, Integer voucherId, String operator, Integer limit, Integer start) {
        // sql查询字段
        //单据号码 收款日期 收款类型 客户名称 收款金额 票据号码 入帐银行 类型 审核人 公司名称 凭证号
        String sqlColumn = "select a.compid,compname=dbo.gf_compid_shortname(a.compid), hbdt=convert(varchar(10),a.hbdt,102), " +
                " a.hbid,a.u8cnt,a.jiuqi_voucher_num,hbempname=dbo.gf_manid_name(a.hbempid), " +
                " isignseq=isnull(a.isignseq,0),iperiod=isnull(a.iperiod,0),isins=a.u8insflag, " +
                " isdel=(case when a.u8insflag=3 and a.u8delflag=1 then 1 else 0 end),isreset=(case when a.u8insflag=2 or a.u8delflag=2 then 1 else 0 end), " +
                " operation=(case when :status in (1,4,5,9) then 'add' when :status=7 then 'delete' when  :status in (6,8) then 'reset' else 'none' end) " +
                " from t_cw_kp_u8hb a inner join t_sys_comp b on a.compid=b.compid left join t_crm_man c on a.hbempid=c.manid ";
        String sqlCount = "select count(1)  from t_cw_kp_u8hb a inner join t_sys_comp b on a.compid=b.compid left join t_crm_man c on a.hbempid=c.manid ";
        String sqlCondition = " where a.hbdt between :dt1 and :dt2  " +
                (companyId != null && companyId != 0 ? " and  a.compid=:companyId " : "") +
                (voucherId != null ? " and a.jiuqi_voucher_num=:voucherId " : "") +
                (operator != null && !operator.isEmpty() ? " and  c.name like :operator " : "") +
                " and ((:status in (1,2,3) and a.u8insflag=:status and isnull(a.jiuqi_insert_flag,1)=1) " +
                " or (:status=4 and a.u8delflag=2) " +
                " or (:status=5 and a.u8delflag=3)" +
                " or (:status in(6,7) and a.jiuqi_insert_flag=:status-4)" +
                " or (:status=8 and a.jiuqi_delete_flag=2)" +
                " or (:status=9 and a.jiuqi_delete_flag=3)) ";

        String orderCondition = "order by a.hbdt desc";

        //获取总记录数
        SQLQuery query = createSQLQuery(sqlCount + sqlCondition);

        query.setDate("dt1", startTime);
        query.setDate("dt2", endTime);
        if (companyId != null && companyId != 0)
            query.setInteger("companyId", companyId);
        if (operator != null && !operator.isEmpty())
            query.setString("operator", "%" + operator + "%");
        if (voucherId != null)
            query.setInteger("voucherId", voucherId);
        query.setInteger("status", status);

        Integer total = (Integer) query.uniqueResult();
        List<Object[]> result = null;

        if (total > 0) {
            //获取列表数据
            query = createSQLQuery(sqlColumn + sqlCondition + orderCondition);

            query.setDate("dt1", startTime);
            query.setDate("dt2", endTime);
            if (companyId != null && companyId != 0)
                query.setInteger("companyId", companyId);
            if (operator != null && !operator.isEmpty())
                query.setString("operator", "%" + operator + "%");
            if (voucherId != null)
                query.setInteger("voucherId", voucherId);
            query.setInteger("status", status);

            result = query.setFirstResult(start).setMaxResults(limit).list();
        }
        //返回值
        Page<Object[]> page = new Page<Object[]>();
        page.setTotalCount(total);
        if (result != null && !result.isEmpty())
            page.setResult(result);
        return page;
    }

    public List getVoucher(Integer id) {
        String sql = "{call sp_jiuqi_voucher_invoice(?,?,?)}";
        List<Object[]> list = createSQLQuery(sql, id, null, null).list();
        return list;
    }


    /**
     * 收付款凭证主表信息
     *
     * @param id 收付款ID
     */
    public List getVoucherMaster(Integer id) {
        String sql = "select dbo.gf_cw_sys_csign(dbo.gf_sys_cwcompid_by_compid(k.compid)," +
                "dbo.gf_cw_sys_isignseq(k.compid,2)),c.company_name,c.company_code " +
                "from t_jiuqi_company c,t_cw_kp k where c.company_id=k.compid and k.kpid=?";
        Object[] result = (Object[]) createSQLQuery(sql, id).uniqueResult();
        if (result != null)
            return Arrays.asList(result);
        return null;
    }

    public List getRevokeInfo(Integer id) {
        String sql = "select k.jiuqi_uuid,k.jiuqi_voucher_id,c.company_code from t_cw_kp k,t_jiuqi_company c where k.compid=c.company_id and k.kpid=?";
        Object[] result = (Object[]) createSQLQuery(sql, id).uniqueResult();
        if (result != null)
            return Arrays.asList(result);
        return null;
    }

    public List getInvoiceMerge(Integer id) {
        String sql = "select kpid,kpno,custname=dbo.gf_custid_name(custid),net,tax,yfje from t_cw_kp where hbid=?";
        List<Object[]> result = (List<Object[]>) createSQLQuery(sql, id).list();
        if (result != null)
            return result;
        return null;
    }
}
